﻿CREATE PROCEDURE [dbo].[utl_CreateSPUpd]
@Table [sysname], @ParentObject [sysname]=NULL
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ID                   INT
	DECLARE @SQL                  NVARCHAR(MAX)
	DECLARE @ColumnSelectList     VARCHAR(MAX)
	DECLARE @ColumnUpdateList     VARCHAR(MAX)
	DECLARE @ColumnVariablesList  VARCHAR(MAX)
	DECLARE @WhereClause          VARCHAR(MAX)
	DECLARE @WhereClause2         VARCHAR(MAX)
	DECLARE @ProcedureName        sysname
	DECLARE @ColumnName           sysname
	DECLARE @Datatype             sysname
	DECLARE @QuotedColumnName     sysname
	DECLARE @VariableColumnName   sysname
	DECLARE @Schema				  sysname
	DECLARE @IsIdentity           BIT
	DECLARE @IsTimestamp          BIT
	DECLARE @IsComputed           BIT
	DECLARE @IsNullable           BIT
	DECLARE @NumericPrecision     SMALLINT
	DECLARE @NumericScale         SMALLINT
	DECLARE @MaxLength            INT
	
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
	
	DECLARE @CRLF VARCHAR(2); SET @CRLF = CHAR(13) + CHAR(10)
	DECLARE @TAB VARCHAR(1); SET @TAB = CHAR(9)
	
	DECLARE @INDENT INT; SET @INDENT = 4
	
	DECLARE @Out TABLE (
				ID INTEGER IDENTITY(1, 1) PRIMARY KEY, 
				Line VARCHAR(MAX)
			)
	
	DECLARE @Columns  TABLE (
	            ID INTEGER IDENTITY(1, 1) PRIMARY KEY,
	            ColumnName sysname,
	            QuotedColumnName sysname,
	            VariableColumnName sysname,
	            Datatype sysname,
	            MaxLength INT,
	            IsIdentity BIT,
	            IsTimestamp BIT,
	            IsComputed BIT,
	            IsPartOfPrimaryKey BIT,
	            IsNullable BIT,
	            NumericPrecision SMALLINT,
	            NumericScale SMALLINT
	        )
	
	-- make sure table exists    
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not exist.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 1
	END
	
	-- make sure table has a primary key
	IF dbo.fn_TableHasPrimaryKey(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not have a primary key.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 2
	END
	
	-- make sure table has a timestamp column
	IF dbo.fn_TableHasTimestamp(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not have a timestamp column.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 3
	END
	
	IF @ParentObject IS NULL
	BEGIN
	    EXECUTE [dbo].[utl_CreateSPScriptTable]
		SET @ParentObject = OBJECT_NAME(@@PROCID)
	END
	
	-- build name of stored procedure to create
	SET @Schema = ISNULL(PARSENAME(@Table, 2), 'dbo')
	SET @ProcedureName = @Schema + '.usp_' + @Table + '_Upd'

	SET @SQL = 'DECLARE @True  BIT; SET @True  = 1'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'DECLARE @False BIT; SET @False = 0'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	-- force any existing procedure to be deleted
	SET @SQL = 'IF dbo.fn_ProcedureExists(''' + @ProcedureName + ''') = @True'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = SPACE(@INDENT) + 'DROP PROCEDURE ' + dbo.fn_QuoteName(@ProcedureName)
	INSERT INTO @Out (Line) VALUES (@SQL)
    
    SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)
    
    SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	-- create a table of column names and certain attributes
	INSERT INTO @Columns
	  (
	    ColumnName,
	    QuotedColumnName,
	    VariableColumnName,
	    Datatype,
	    MaxLength,
	    IsIdentity,
	    IsTimestamp,
	    IsComputed,
	    IsPartOfPrimaryKey,
	    IsNullable,
	    NumericPrecision,
	    NumericScale
	  )
	SELECT ColumnName,
	       QuotedColumnName,
	       VariableColumnName,
	       Datatype,
	       MaxLength,
	       IsIdentity,
	       IsTimestamp,
	       IsComputed,
	       IsPartOfPrimaryKey,
	       IsNullable,
	       NumericPrecision,
	       NumericScale
	FROM   dbo.fn_ColumnsAndAttributes(@Table)
	
	-- build proc name
	SET @SQL = 'CREATE PROCEDURE ' + dbo.fn_QuoteName(@ProcedureName) + ' ('
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	-- build lists of column names for select
	SELECT @ID = MIN(ID)
	FROM   @Columns
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName,
	           @QuotedColumnName = QuotedColumnName,
	           @VariableColumnName = VariableColumnName,
	           @Datatype = Datatype,
	           @MaxLength = MaxLength,
	           @IsIdentity = IsIdentity,
	           @IsTimestamp = IsTimestamp,
	           @IsComputed = IsComputed,
	           @IsNullable = IsNullable
	    FROM   @Columns
	    WHERE  ID = @ID
	    -- list of columns for select
	    SET @ColumnSelectList = ISNULL(@ColumnSelectList + ', ', '') + @QuotedColumnName
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	END
	
	-- tack on housekeeping columns for the refresh select
	SET @ColumnSelectList = @ColumnSelectList + dbo.fn_HousekeepingColumnList()
	
	-- build list of column names for parameters
	SELECT @ID = MIN(ID)
	FROM   @Columns
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName,
	           @QuotedColumnName = QuotedColumnName,
	           @VariableColumnName = VariableColumnName,
	           @Datatype = Datatype,
	           @MaxLength = MaxLength,
	           @IsIdentity = IsIdentity,
	           @IsTimestamp = IsTimestamp,
	           @IsComputed = IsComputed,
	           @IsNullable = IsNullable,
	           @NumericPrecision = NumericPrecision,
	           @NumericScale = NumericScale
	    FROM   @Columns
	    WHERE  ID = @ID
	    -- build list of parameters with datatypes and lengths
	    SET @SQL = SPACE(@INDENT) + @VariableColumnName
	    SET @SQL = @SQL + ' ' + UPPER(@Datatype)
	    IF @Datatype IN ('VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR')
		BEGIN
			IF @MaxLength = -1
			BEGIN
		        SET @SQL = @SQL + '(MAX)'
			END
			ELSE
			BEGIN
		        SET @SQL = @SQL + '(' + CAST(@MaxLength AS VARCHAR(4)) + ')'
			END			
		END
	    
	    IF @Datatype IN ('NUMERIC', 'DECIMAL')
	        SET @SQL = @SQL + '(' + CAST(@NumericPrecision AS VARCHAR(2)) + ',' + CAST(@NumericScale AS VARCHAR(2)) + ')'
	    
	    IF @IsNullable = 1
	        SET @SQL = @SQL + ' = NULL'
	    
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	    
	    IF @ID IS NULL
	        SET @SQL = @SQL + ')'
	    ELSE
	        SET @SQL = @SQL + ','
	    
		INSERT INTO @Out (Line) VALUES (@SQL)
	END
	
	SET @SQL = 'AS'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'BEGIN'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'DECLARE @ErrorCode AS INT'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'DECLARE @RowsAffected AS INT'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'UPDATE ' + dbo.fn_QuoteName(@Table) + ' WITH (ROWLOCK) SET'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	-- build list of set statements for update
	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  (
	           (IsIdentity = @False)
	           AND (IsTimestamp = @False)
	           AND (IsComputed = @False)
	       )
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName,
	           @QuotedColumnName = QuotedColumnName,
	           @VariableColumnName = VariableColumnName,
	           @Datatype = Datatype,
	           @MaxLength = MaxLength,
	           @IsIdentity = IsIdentity,
	           @IsTimestamp = IsTimestamp,
	           @IsComputed = IsComputed
	    FROM   @Columns
	    WHERE  ID = @ID
	    
	    SET @SQL = SPACE(@INDENT) + SPACE(3) + @QuotedColumnName + ' = ' + @VariableColumnName
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	           AND (
	                   (IsIdentity = 0)
	                   AND (IsTimestamp = 0)
	                   AND (IsComputed = 0)
	               )
	    
	    IF @ID IS NOT NULL
	        SET @SQL = @SQL + ','
	    
		INSERT INTO @Out (Line) VALUES (@SQL)
	END
	
	-- build where clause based on primary key columns
	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  IsPartOfPrimaryKey = @True
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SET @WhereClause = ISNULL(@WhereClause + ' AND ', '') + '(' + (
	            SELECT QuotedColumnName
	            FROM   @Columns
	            WHERE  ID = @ID
	        ) + ' = ' + (
	            SELECT VariableColumnName
	            FROM   @Columns
	            WHERE  ID = @ID
	        ) + ')'
	    
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	           AND IsPartOfPrimaryKey = @True
	END
	
	-- add concurrency check to where clause
	SET @WhereClause2 = @WhereClause
	SET @WhereClause = @WhereClause + ' AND ' + '(' + dbo.fn_QuoteName(dbo.fn_TimestampColumn(@Table)) 
	    + ' = ' + (
	        SELECT VariableColumnName
	        FROM   @Columns
	        WHERE  IsTimestamp = @True
	    ) + ')'
	
	SET @SQL = SPACE(@INDENT) + 'WHERE ' + @WhereClause
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = SPACE(@INDENT) + 'OPTION (FAST 1)'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'IF @RowsAffected = 0'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = SPACE(@INDENT) + 'RETURN 50001  -- concurrency violation'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'SELECT ' + @ColumnSelectList + ' FROM ' + dbo.fn_QuoteName(@Table)
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = SPACE(@INDENT) + 'WHERE ' + @WhereClause2
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'RETURN @ErrorCode'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'END'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = REPLICATE('-', 80)
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	INSERT INTO CreateSPScriptTable
	  (
	    Line
	  )
	SELECT Line
	FROM   @Out
	ORDER BY
	       ID
	
	IF @ParentObject = OBJECT_NAME(@@PROCID)
	BEGIN
	    SELECT Line
	    FROM   CreateSPScriptTable
	    ORDER BY
	           ID
	END
	
	SET NOCOUNT OFF
	
	RETURN 0
END

